Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# MULTIPATHHIER

The MULTIPATHHIER option specifies that a given cell that contains detail data can have more than one path into a cell that contains aggregated data. Certain calculations require this kind of multiple-path aggregation.

Syntax

MULTIPATHHIER = {YES|NO}

Arguments

YES

  Allows a detail data cell to aggregate in multiple paths to the same ancestor cell.

NO

  (Default) Disallows a detail data cell to aggregate in multiple paths to the same ancestor cell.

Notes

When to Use MULTIPATHHIER

The only time you should set the MULTIPATHHIER option to `YES` is when a calculation requires the use of multiple paths.

Interpreting an XSHIERCK01 Error Message

When you use the AGGREGATE command, dimension hierarchies are automatically checked for circularity. When MULTIPATHHIER is set to `NO`, or when the default of `NO` has not been changed, then the following error message is displayed when a detail data cell uses multiple paths to the same aggregate data cell.

    ERROR: (XSHIERCK01) One or more loops have been detected
    in your hierarchy n over N. The loops include 2 items
    (UNDIRECTED: X and Y).

In the preceding error message, `X` is the name of the detail data cell, and `Y` is the name of the ancestor cell into which the detail data cell takes more than one path to aggregate. For more information, see Example 18-10, "Defining Multiple Paths in a Hierarchy".

This error message is displayed because the multiple paths taken by the detail data cell have been interpreted as a circular hierarchy. When this is a mistake and you did not intend to create multiple paths, then change the hierarchy. Otherwise, set the MULTIPATHHIER option to `YES`.

Examples

Example 18-10 Defining Multiple Paths in a Hierarchy

This example shows how you can define multiple paths in a hierarchy, the error message that results when you attempt to aggregate data, how to interpret that message, and how to resolve the problem.

The following statements create two paths from a detail data cell to an ancestor cell that contains aggregated data.

    DEFINE geog TEXT DIMENSION
    DEFINE path INTEGER DIMENSION
    DEFINE geog.geog RELATION geog <geog path>
    MAINTAIN geog ADD 'A1' 'b1' 'b2' 'Top'
    MAINTAIN path ADD 2
    geog.geog(geog 'A1' path 1) = 'B1'
    geog.geog(geog 'A1' path 2) = 'B2'
    geog.geog(geog 'B1' path 1) = 'Top'
    geog.geog(geog 'B2' path 1) = 'Top'

First, a geography dimension named `geog` and a second dimension named `path` are defined.

A relation named `geog.geog` is defined, in which the geography dimension is dimensioned by itself and the `path` dimension.

Dimension values named `A1`, `B1`, `B2`, and `Top` are added to the `geog` dimension.

Two dimension values are added to the `path` dimension. Because `path` was defined with an `INTEGER` data type, the dimension values that are automatically assigned to it are the `INTEGER` values `1` and `2`.

Finally, the hierarchy for the `geog` dimension is created. The `A1` dimension value is the detail data. The `B1` and `B2` dimension values are the second level of the hierarchy. The `Top` dimension value is the top of the hierarchy.

`A1` has two aggregation paths: `A1` aggregates into `B1`, which aggregates into `Top`; `A1` aggregates into `B2`, which aggregates into `Top`.

The following statements define a variable named `myvar`, assign a data value of `1` to its detail data level (`A1`), and define an aggmap for that variable.

    DEFINE myvar INTEGER VARIABLE <geog>
    myvar(geog 'A1') = 1
    DEFINE myvar.aggmap <geog>
    AGGMAP 'RELATION geog.geog'

An attempt to aggregate `myvar` generates the following error message.

    AGGREGATE myvar USING myvar.aggmap
    ERROR: (XSHIERCK01) One or more loops have been detected
    in your hierarchy GEOG.GEOG over GEOG. The loops include 2
    items (UNDIRECTED: A1 and TOP).

The multiple paths of aggregation that have been created for `A1` have been interpreted as a circular hierarchy, because the MULTIPATHHIER option is currently set to `NO`.

When you had made a mistake and created these multiple paths by mistake, you would fix the problem in the hierarchy.

However, in this case, the multiple paths have been created because a calculation requires them. Therefore, the solution is to set MULTIPATHHIER to `YES`. Now you can execute the AGGREGATE command without error.